#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2014年12月18日

@author: chenyongbing
'''
import sys,os,copy
current_dir = os.path.dirname(__file__)
import logging
sys.path.append(os.path.join(current_dir,'../../'))

from base.PyMySQL import ConnectMySQL
from base.PyEmail import *
from base.localConfig import baseLocalConfig
from localConfig import LocalConfig
myLocalConfig = LocalConfig()
import sys,datetime
retail_host = baseLocalConfig.mysqlConfig['retail']['host']
retail_user = baseLocalConfig.mysqlConfig['retail']['user']
retail_passwd = baseLocalConfig.mysqlConfig['retail']['passwd']
retail_db = baseLocalConfig.mysqlConfig['retail']['db']

import openpyxl
myRetail = ConnectMySQL(retail_host,retail_user,retail_passwd,retail_db)



class RecordApStatus(object):
    '''
        记录设备状态
    '''
    def __init__(self):
        '''
        Constructor
        '''
        pass
        
    def get_ap_with_shop_and_company(self,name='sta_status',groupList=[]):
        if groupList==[]:
            company_group = myLocalConfig.rules[name]['comp_group']
        else:
            company_group = ','.join(groupList)
        
        query = 'select c.name,g.name,g.province,g.city,g.distribution,d.deviceid from devices d,group_device gd ,groups g ,companies c where gd.device_id=d.id and g.id=gd.group_id and c.id=d.company_id order by c.name,g.name'    
        if company_group=='' or company_group==None: 
            logging.info('Select Devices From Tab No Group.')
        else:
            query = 'select c.name,g.name,g.province,g.city,g.distribution,d.deviceid from devices d,group_device gd ,groups g ,companies c where gd.device_id=d.id and g.id=gd.group_id and c.id=d.company_id and d.company_id in (select company_id from groups where id in (%s) ) order by c.name,g.name'%company_group    
            logging.info('Select Devices From Tab By Group:%s.'%company_group)
        rets = myRetail.SelectAll(query)
        #print query
        
        datas = {}
        if rets!=None:
            logging.info('Return %s Devices From Tab.'%(len(rets)))
            for company,shop_name,shop_prov,shop_city,shop_dist,device in rets:
                datas[device] = {'company':company,'shop_name':shop_name,'shop_prov':shop_prov,'shop_city':shop_city,'shop_dist':shop_dist}
            
            
            return datas
        else:
            logging.error('Not Return Any Devices From Tab.')
        return datas
    
    
    
    def get_sta_status(self,tab='aaa_sta_online_offline_log',datas=[],startTime=None,endTime=None):
        query = 'select ap_mac,sta_mac,online_time,offline_time from %s'%tab
        today = datetime.date.today().strftime('%Y-%m-%d')
        year = int(today[:4])
        month = int(today[5:7])
        day = int(today[8:10])
        if startTime == None:
            startTime = datetime.datetime(year,month,day,0,0,0)
        
        if endTime ==None:
            endTime = datetime.datetime(year,month,day,23,59,59)
        
        query = query + ' where online_time between "%s" and "%s"'%(startTime,endTime)
        
        logging.info('Query:%s.'%query)
        
        devices = datas.keys()
        if devices!=[]:
            query = query + ' and ap_mac in (%s)'%('"'+'","'.join(devices)+'"')
            logging.info('Query : where ap_mac in %s aps.'%(len(devices)))
        rets = myRetail.SelectAll(query)
        ap_sta_datas = {}
        
        if rets==None:
            logging.error('Not Return Any Sta Records From Tab.')
        else:
            logging.info('Return %s Sta Records From Tab.'%(len(rets)))
            for ap_mac,sta_mac,online_time,offline_time in rets:
                sta_data = {'online_time':online_time,'offline_time':offline_time}
                
                if not ap_sta_datas.has_key(ap_mac):
                    
                    ap_sta_data = {}
                    ap_sta_datas[ap_mac] = datas[ap_mac]
                    ap_sta_datas[ap_mac]['datas'] = {}
                    #print ap_mac,ap_sta_datas[ap_mac]
                    
                else:
                    ap_sta_data = ap_sta_datas[ap_mac]['datas']
                if not ap_sta_data.has_key(sta_mac):
                    sta_datas = []
                else:
                    sta_datas = ap_sta_data[sta_mac]
                sta_datas.append(sta_data)
                ap_sta_data[sta_mac] = sta_datas
                ap_sta_datas[ap_mac]['datas'] = ap_sta_data
            
        return ap_sta_datas
    
    
    
    def export_sta_status_to_excel(self,name='sta_status',ap_sta_datas={},export_file=''):
        wb = openpyxl.load_workbook(myLocalConfig.rules[name]['template_file'])
        ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
        i=0
        start_row = 1
        for ap_mac ,ap_sta_data in ap_sta_datas.iteritems():
            shop_name = ap_sta_data['shop_name']
            shop_prov = ap_sta_data['shop_prov']
            shop_city = ap_sta_data['shop_city']
            shop_dist = ap_sta_data['shop_dist']
            #print ap_mac,ap_sta_data
            for sta_mac ,sta_datas in ap_sta_data['datas'].iteritems():
                for sta_data in sta_datas:
                    i+=1
                    online_time = sta_data['online_time']
                    offline_time = sta_data['offline_time']
                    if online_time==None:
                        online_time = '-'
                        online_sec = 0
                    else:
                        online_sec = int(online_time.strftime('%s'))
                        online_time = online_time.strftime('%Y-%m-%d %H:%M:%S')
                    if offline_time==None:
                        offline_time = '-'
                        offline_sec = 0
                    else:
                        offline_sec = int(offline_time.strftime('%s'))
                        offline_time = offline_time.strftime('%Y-%m-%d %H:%M:%S')
                    
                    online_dur = '-'
                    if online_time !='-' and offline_time!='-':
                        online_dur = (offline_sec - online_sec)/60
                        
                    
                    
                    #序号
                    ws.cell(row=start_row+i,column=1).value = i
                    #终端mac
                    ws.cell(row=start_row+i,column=2).value = sta_mac
                    #上线时间
                    ws.cell(row=start_row+i,column=3).value = online_time
                    #下线时间
                    ws.cell(row=start_row+i,column=4).value = offline_time
                    #上网时长（单位:分）
                    ws.cell(row=start_row+i,column=5).value = online_dur
                    #设备mac
                    ws.cell(row=start_row+i,column=6).value = ap_mac
                    #门店名称
                    ws.cell(row=start_row+i,column=7).value = shop_name
                    #所属省份
                    ws.cell(row=start_row+i,column=8).value = shop_prov
                    #所属城市
                    ws.cell(row=start_row+i,column=9).value = shop_city
                    #所属区域
                    ws.cell(row=start_row+i,column=10).value = shop_dist
            
        logging.info('Save Xls File To %s'%export_file)
        wb.save(export_file)
    def sta_status_and_send_mail(self,name='sta_status',day=None,startTime=None,endTime=None,export_file='',groupList=[]):
        mail_user = myLocalConfig.email_user
        mail_pass = myLocalConfig.email_passwd
        mail_host = myLocalConfig.email_smtp
        mail_postfix = myLocalConfig.email_postfix
        sub = '认证用户列表'
        content = ''
        #print  myLocalConfig.rules
        to_list = myLocalConfig.rules[name]['mail_to_list']
        while 1:
            if os.path.exists(export_file):break
            try:
                shopdatas = myRecordApStatus.get_ap_with_shop_and_company(name=rule,groupList=groupList)
                break
            except:
                pass
        while 1:
            if os.path.exists(export_file):break
            try:
                ap_sta_datas = myRecordApStatus.get_sta_status(datas=shopdatas,startTime=startTime,endTime=endTime)
                break
            except:pass
        while 1:
            if os.path.exists(export_file):
                logging.info('Xls File %s Alread Exists.'%export_file)
                break
            try:
                myRecordApStatus.export_sta_status_to_excel(ap_sta_datas=ap_sta_datas, export_file=export_file)
            except:pass
            
            
        while 1:
            atts = [export_file]
            ret = send_mail_with_atts(mail_user,mail_pass,mail_host,mail_postfix,to_list,sub,content,atts=atts)
            if ret==True:
                send_status = True
                logging.info("send mail successed.")
                return
            else:
                wait_time = 10
                logging.info("send mail faild, wait %s seconds try again"%wait_time)
                time.sleep(wait_time)
            
if __name__ == '__main__':
    import argparse,re,datetime,time
    parser = argparse.ArgumentParser(description='args')
    parser.add_argument('--day',metavar=u"day",default=None)
    parser.add_argument('--startTime',metavar=u"startTime:2015-01-01 00:00:00",default=None)
    parser.add_argument('--endTime',metavar=u"endTime:2015-01-02 12:00:00",default=None)
    parser.add_argument('--rule',metavar=u"sta_status",default='sta_status')
    parser.add_argument('--name',metavar=u"name",default=None)
    parser.add_argument('--groups',metavar=u"groups",default=None)
    args = parser.parse_args()
    startTime = args.startTime
    endTime = args.endTime
    day =args.day
    name = args.name
    rule = args.rule
    groups = args.groups
 
    myRecordApStatus = RecordApStatus()
    if day==None:
        day = datetime.date.today().strftime('%Y-%m-%d')
    if startTime==None or endTime==None:
        startTime = day + ' 00:00:00'
        endTime = day + ' 23:59:59'    
    
    #day = "2014-12-18"
    export_path = myLocalConfig.rules[rule]['export_path']

    
    rgroups = myLocalConfig.rules[rule]['comp_group']
    if re.search('sta_status',rule):
        if groups==None and rgroups=='' :
            logging.error('You Must Enter A Group.')
            sys.exit()
        elif rgroups=='':
            groupList = groups.split(',')
        elif groups==None:
            groupList = rgroups.split(',')
        else:
            groupList = groups.split(',')
    else:
        groupList = []
        
    if name==None:
        name = myLocalConfig.rules[rule]['name']
    export_file = os.path.join(export_path , '认证用户列表_%s_%s.xlsx'%(name,day))
    
    myRecordApStatus.sta_status_and_send_mail(name=rule, day=day, startTime=startTime, endTime=endTime, export_file=export_file, groupList=groupList)
    
    
    
    
    